package store.dao;

import store.po.Category;
import store.utils.Pager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CategoryDAO {


    /**
     * 根据参数id查询t_category，将结果封装成Category返回
     * @param id
     * @return
     * @throws Exception
     */
    public Category findById(int id) throws Exception {
        Category c=null;
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select * from t_category where id=? ");
            ps.setInt(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                c=new Category();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setDescription(rs.getString("description"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return c;
    }

    /**
     * 根据参数name模糊查询表t_category，将结果封装成List返回
     * @param name
     * @return
     * @throws Exception
     */
    public List<Category> findCategories(String name) throws Exception {
        List<Category> list=new ArrayList<>();
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select * from t_category where name like ? ");
            ps.setString(1,"%"+name+"%");
            rs=ps.executeQuery();
            while (rs.next()){
                Category c=new Category();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setDescription(rs.getString("description"));
                list.add(c);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return list;
    }

    /**
     * 根据参数name模糊查询供应商表t_category，对查询结果进行分页，将结果封装成List返回
     * @param name  查询条件
     * @param pager 分页对象
     * @return
     * @throws Exception
     */
    public List<Category> findCategories(String name, Pager pager) throws Exception {
        List<Category> list=new ArrayList<>();
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select count(id) as total from t_category where name like ?");
            ps.setString(1,"%"+name+"%");
            rs=ps.executeQuery();
            if(rs.next()){
                pager.setTotal(rs.getInt("total"));
            }
            ps=con.prepareStatement("select * from t_category where name like ? limit ?,?");
            ps.setString(1,"%"+name+"%");
            ps.setInt(2,(pager.getCurrentPage()-1)*pager.getPageSize());
            ps.setInt(3,pager.getPageSize());
            rs=ps.executeQuery();
            while (rs.next()){
                Category c=new Category();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setDescription(rs.getString("description"));
                list.add(c);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return list;
    }

    /**
     * 将Category类型参数c的值保存成t_category表中的一条记录
     * @param c
     * @throws Exception
     */
    public void save(Category c) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("insert into t_category value (null ,?,?)");
            ps.setString(1,c.getName());
            ps.setString(2,c.getDescription());
 
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }

    /**
     * 将Category对象c更新到t_category表中
     * @param c
     * @throws Exception
     */
    public void update(Category c) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("update t_category set name=?,description=? where id=?");
            ps.setString(1,c.getName());
            ps.setString(2,c.getDescription());
            ps.setInt(3,c.getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }
    /**
     * 根据id删除t_category表中对应的记录
     * @param id
     * @throws Exception
     */
    public void delete(int id) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("delete  from t_category where id=?");
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }
}
